# -*- coding: utf-8 -*-
import cx_Oracle
con = cx_Oracle.connect('gis/ndtvgis@192.168.20.79/GIS')
# print con.version
cur = con.cursor()
odf表        teodf
光交接表        tegjjx
光终端盒表       tegzdh
光分路器表       teobd
olt表            teolt
odf模块表      teodm
光设备模块表      tegldzb
光设备端子表      tegjsbdz
光设备跳纤表      tegjsbjmp
光缆表         tegl
光缆段表        tegld
光缆段纤芯表  tegldqx
光设备关联表      tegjsbgl
光纤熔接表       tegqrj
光缆接头表       tegljt
光缆预留表       teglyl
光缆盘留表       teglpl
光缆节连接点表     tegljljd
光缆节表        teglj
光路表     tegr



# tegjsbdz 面板端子
# teqy 区域 type=2
# tejz 分前端 zqybm=teqy.bm
# tejf 机房 jflx=1,jzbm=tejz.bm
# tegjjx 光交 jzbm=tejz.bm
# teodf ODF sysm=0,jfbm=tejf.bm  不确定 或者 sysm=0,jzbm=tejz.bm
# teodm odf和olt的机框号在这里

# ODF和机房的关系 
# tepnt 的type=87是odf，ztid=tejf.id0

# tegjsbdz 端子跳接关系表 
# SELECT * FROM tegjsbjmp where (rdevid=14812 or cdevid=14812) and (rdz=217 or cdz=217);

tegldzb 光设备模块表 关联了下面这些设备类型的数据
30
6
11
2
21
20
26
17
27
18
10
19
9


# select dz.id0,dz.devid,dz.mkrow,dz.mkcol,dz.dzrow,dz.dzcol,dz.dz,dz.bz,jmp.id0,jmp.rdz,jmp.cdz,jmp.rdevid,jmp.cdevid from tegjsbdz dz left join (select * from tegjsbjmp jmp where jmp.rdevid=14812 or jmp.cdevid=14812) jmp on jmp.rdz=dz.dz or jmp.cdz=dz.dz where (dz.devid='14812' and dz.mkcol=1) order by dz.mkrow,dz.dzcol;


# tegjsbgl 端子成端关系表 devtype=2 是gj devtype=1 是ODF kh 是框号，只有odf有框 kh，gj没有框号 kh
# tegjsbjmp rdevlx 1是odf ，2 是gj，9是OBD，11是OLT
# SELECT cd.*,gld.mc FROM tegjsbgl cd left join  tegld gld on cd.gldid=gld.id0 where cd.devid=14812;
# SELECT * FROM tegjsbgl cd where cd.devid=14812;
# SELECT * FROM tegld where id0=3084;





'''

SELECT * FROM tegjsbjmp where (rdevid=14789 or cdevid= 14789 ) and (rdevlx=1 and cdevlx=1) -- ODF的跳接关系
union

SELECT * FROM tegjsbjmp where rdevid=14789 and rdevlx=1 and cdevlx=9 --ODF跳接到OBD的关系
union

SELECT * FROM tegjsbjmp where rdevid=14789 and rdevlx=1 and cdevlx=11 --ODF跳接到OLT的关系
union

SELECT * FROM tegjsbjmp where cdevid=14789 and cdevlx=1 and rdevlx=9  --OBD跳接到ODF的关系
union

SELECT * FROM tegjsbjmp where cdevid=14789 and cdevlx=1 and rdevlx=11 --OLT跳接到ODF的关系
'''



SELECT dz.id0 id0 ,obd.bm||' '||dz.dz pw FROM tegjsbdz dz,teobd obd where dz.devid=20052 and dz.devid=obd.id0  --obd的端子id和pw

SELECT * FROM tegjsbjmp jmp where jmp.rdevid=16232 and jmp.cdevid=16232  --gj内部的跳接关系
union all
SELECT * FROM tegjsbjmp jmp where jmp.rdevid=16232 and jmp.cdevlx=9  --从gj跳到obd的关系
union all
SELECT * FROM tegjsbjmp jmp where jmp.cdevid=16232 and jmp.rdevlx=9  --从obd跳到光交的关系



光交上有跳接关系的所有端子 和 没有跳接关系的端子 按框分开来的合集

select dz.id0,dz.devid,dz.mkrow,dz.mkcol,dz.dzrow,dz.dzcol,dz.dz,dz.zt,dz.bz,'' pw,jmp.jmp from tegjsbdz dz left join 
(
select dz.id0,dz.devid,dz.mkrow,dz.mkcol,dz.dzrow,dz.dzcol,dz.dz,dz.zt,dz.bz,'' pw,jmp.cdz jmp from tegjsbdz dz ,tegjsbjmp jmp
where dz.devid = 16232 and dz.mkcol=1 and jmp.rdevid=16232 and jmp.cdevid=16232 and jmp.rdz=dz.dz
union all
select dz.id0,dz.devid,dz.mkrow,dz.mkcol,dz.dzrow,dz.dzcol,dz.dz,dz.zt,dz.bz,'' pw,jmp.rdz jmp from tegjsbdz dz ,tegjsbjmp jmp
where dz.devid = 16232 and dz.mkcol=1 and jmp.rdevid=16232 and jmp.cdevid=16232 and jmp.cdz=dz.dz
union all
select dz.id0,dz.devid,dz.mkrow,dz.mkcol,dz.dzrow,dz.dzcol,dz.dz,dz.zt,dz.bz,'' pw,jmp.cdz jmp from tegjsbdz dz ,tegjsbjmp jmp
where dz.devid = 16232 and dz.mkcol=1 and jmp.rdevid=16232 and jmp.cdevlx=9 and jmp.rdz=dz.dz
union all
select dz.id0,dz.devid,dz.mkrow,dz.mkcol,dz.dzrow,dz.dzcol,dz.dz,dz.zt,dz.bz,'' pw,jmp.rdz jmp from tegjsbdz dz ,tegjsbjmp jmp
where dz.devid = 16232 and dz.mkcol=1 and jmp.cdevid=16232 and jmp.rdevlx=9 and jmp.cdz=dz.dz
) jmp on dz.id0=jmp.id0 where dz.devid = 16232 and dz.mkcol=1

union all

select dz.id0,dz.devid,dz.mkrow,dz.mkcol,dz.dzrow,dz.dzcol,dz.dz,dz.zt,dz.bz,'' pw,jmp.jmp from tegjsbdz dz left join 
(
select dz.id0,dz.devid,dz.mkrow,dz.mkcol,dz.dzrow,dz.dzcol,dz.dz,dz.zt,dz.bz,'' pw,jmp.cdz jmp from tegjsbdz dz ,tegjsbjmp jmp
where dz.devid = 16232 and dz.mkcol=2 and jmp.rdevid=16232 and jmp.cdevid=16232 and jmp.rdz=dz.dz
union all
select dz.id0,dz.devid,dz.mkrow,dz.mkcol,dz.dzrow,dz.dzcol,dz.dz,dz.zt,dz.bz,'' pw,jmp.rdz jmp from tegjsbdz dz ,tegjsbjmp jmp
where dz.devid = 16232 and dz.mkcol=2 and jmp.rdevid=16232 and jmp.cdevid=16232 and jmp.cdz=dz.dz
union all
select dz.id0,dz.devid,dz.mkrow,dz.mkcol,dz.dzrow,dz.dzcol,dz.dz,dz.zt,dz.bz,'' pw,jmp.cdz jmp from tegjsbdz dz ,tegjsbjmp jmp
where dz.devid = 16232 and dz.mkcol=2 and jmp.rdevid=16232 and jmp.cdevlx=9 and jmp.rdz=dz.dz
union all
select dz.id0,dz.devid,dz.mkrow,dz.mkcol,dz.dzrow,dz.dzcol,dz.dz,dz.zt,dz.bz,'' pw,jmp.rdz jmp from tegjsbdz dz ,tegjsbjmp jmp
where dz.devid = 16232 and dz.mkcol=2 and jmp.cdevid=16232 and jmp.rdevlx=9 and jmp.cdz=dz.dz
) jmp on dz.id0=jmp.id0 where dz.devid = 16232 and dz.mkcol=2




select * from tegjsbdz dz left join () jmp on  where dz.devid = 16232 and dz.mkcol=1



--------------------------



得到r有跳接的端子
SELECT * FROM tegjsbjmp jmp left join
tegjsbdz dz on dz.devid=jmp.rdevid and dz.devtype=jmp.rdevlx and dz.dz=jmp.rdz
 where jmp.rdevid=16232 and jmp.rdevlx=2
组合 
 union all
得到c有跳接的端子
SELECT * FROM tegjsbjmp jmp left join
tegjsbdz dz on dz.devid=jmp.cdevid and dz.devtype=jmp.cdevlx and dz.dz=jmp.cdz
where jmp.cdevid=16232 and jmp.cdevlx=2
--------------------------------

得到r端子id0和r端子跳接的对端c端子的id0,devid,devtype

SELECT rjmp.id0 adz,
       cdz.devid bdevid,
       cdz.devtype bdevtype, 
       cdz.id0  bdz 
FROM   (SELECT rdz.id0, 
               jmp.rdz, 
               jmp.rdevlx, 
               jmp.rdevid, 
               jmp.cdz, 
               jmp.cdevlx, 
               jmp.cdevid 
        FROM   tegjsbjmp jmp 
               left join tegjsbdz rdz 
                      ON rdz.devid = jmp.rdevid 
                         AND rdz.devtype = jmp.rdevlx 
                         AND rdz.dz = jmp.rdz 
        WHERE  jmp.rdevid = 16232 and jmp.rdevlx=2) rjmp 
       left join tegjsbdz cdz 
              ON cdz.devid = rjmp.cdevid 
                 AND cdz.devtype = rjmp.cdevlx 
                 AND cdz.dz = rjmp.cdz 
组合起来
union all
得到c端子id0和c端子跳接的对端r端子的id0,devid,devtype

SELECT cjmp.id0 adz, 
       rdz.devid bdevid,
       rdz.devtype bdevtype, 
       rdz.id0  bdz 
FROM   (SELECT cdz.id0, 
               jmp.cdz, 
               jmp.cdevlx, 
               jmp.cdevid, 
               jmp.rdz, 
               jmp.rdevlx, 
               jmp.rdevid 
        FROM   tegjsbjmp jmp 
               left join tegjsbdz cdz 
                      ON cdz.devid = jmp.cdevid 
                         AND cdz.devtype = jmp.cdevlx 
                         AND cdz.dz = jmp.cdz 
        WHERE  jmp.cdevid = 16232 and jmp.cdevlx=2) cjmp 
       left join tegjsbdz rdz 
              ON rdz.devid = cjmp.rdevid 
                 AND rdz.devtype = cjmp.rdevlx 
                 AND rdz.dz = cjmp.rdz 

--------------------------------------------
老的
得到b端所有非当前设备的devtype和devid
a端为当前设备，例子里为gj
SELECT bdevtype,bdevid FROM (
 
SELECT rjmp.id0 adz,
       cdz.devid bdevid,
       cdz.devtype bdevtype, 
       cdz.id0  bdz 
FROM   (SELECT rdz.id0, 
               jmp.rdz, 
               jmp.rdevlx, 
               jmp.rdevid, 
               jmp.cdz, 
               jmp.cdevlx, 
               jmp.cdevid 
        FROM   tegjsbjmp jmp 
               left join tegjsbdz rdz 
                      ON rdz.devid = jmp.rdevid 
                         AND rdz.devtype = jmp.rdevlx 
                         AND rdz.dz = jmp.rdz 
        WHERE  jmp.rdevid = 16232 and jmp.rdevlx=2) rjmp 
       left join tegjsbdz cdz 
              ON cdz.devid = rjmp.cdevid 
                 AND cdz.devtype = rjmp.cdevlx 
                 AND cdz.dz = rjmp.cdz 
union all
SELECT cjmp.id0 adz, 
       rdz.devid bdevid,
       rdz.devtype bdevtype, 
       rdz.id0  bdz 
FROM   (SELECT cdz.id0, 
               jmp.cdz, 
               jmp.cdevlx, 
               jmp.cdevid, 
               jmp.rdz, 
               jmp.rdevlx, 
               jmp.rdevid 
        FROM   tegjsbjmp jmp 
               left join tegjsbdz cdz 
                      ON cdz.devid = jmp.cdevid 
                         AND cdz.devtype = jmp.cdevlx 
                         AND cdz.dz = jmp.cdz 
        WHERE  jmp.cdevid = 16232 and jmp.cdevlx=2) cjmp 
       left join tegjsbdz rdz 
              ON rdz.devid = cjmp.rdevid 
                 AND rdz.devtype = cjmp.rdevlx 
                 AND rdz.dz = cjmp.rdz 
 
) b where bdevid<>16232 and bdevtype<>2
group by bdevtype,bdevid


----------------------

新的
得到b端所有非当前设备的devtype和devid
a端为当前设备，例子里为gj
SELECT bdevlx, 
       bdevid 
FROM   (SELECT cdevlx bdevlx, 
               cdevid bdevid 
        FROM   tegjsbjmp rjmp 
        WHERE  rdevid = 16232 
               AND rdevlx = 2 
        UNION ALL 
        SELECT rdevlx bdevlx, 
               rdevid bdevid 
        FROM   tegjsbjmp cjmp 
        WHERE  cdevid = 16232 
               AND cdevlx = 2) 
WHERE  bdevlx <> 2 
       AND bdevid <> 16232 
GROUP  BY bdevlx, 
          bdevid 



----------------------------
需要测试的情况
gj 16236 百丈东路-海晏南路光交  B面中间空盘
gj 16232 河清北路-惊驾东路光交  A面有跳接obd
gj 14844 义路（维科集团）光交   A\B面上面有空盘
gj 14795 翠柏路-后河巷光交      A面有27盘，B面26盘


-----------------------------


for 面板 in 光交:
    print 面板号（转义）
    print table
    for 盘 in 光交.面板:
        print tr
        print td,盘号（转义）
        if 盘.端子==空：
            print td,colspan=当前面板每盘最大端子数
        else:
            for 端子 in 光交.面板.盘
                print td,端子信息
            
----------------------------
需要测试的情况
odf 14765 传输中心南ODF10号柜         8框
odf 14766 传输中心中机房中ODF1号柜    5框12盘4端子
odf 14784 竹福园机房ODF01             跳接到其他odf和obd
odf 14789 科技园区机房ODF02           有跳到OLT
odf 14737 广电大厦8楼机房ODF01        4盘12端子    


-----------------------------



cur.execute("select * from tegjjx gj,tegjsbdz dz where gj.id0='14795' and gj.id0=dz.devid order by dz.mkcol,dz.mkrow,dz.dzcol")
rows=cur.fetchall()
print len(rows)
# for row in rows:
    # print row
cur.close()
con.close()